create database G6_Online_Banking
go
--drop database G6_Online_Banking
use G6_Online_Banking
go
create table Customer_Types
(
	customer_type_code int primary key identity(1,1),
	customer_type_description nvarchar(50) not null
)
go
create table Customers
(
	customer_id int primary key identity(1,1),
	customer_type_code int foreign key (customer_type_code) references Customer_Types (customer_type_code),
	customer_name nvarchar(50) not null,
	customer_dob datetime not null,
	customer_address nvarchar(50) not null,
	customer_phone nvarchar(15) not null,
	customer_email nvarchar(50) not null,
	[user_id] int unique not null,
	password int not null,
	transaction_password nvarchar(6),
	status bit default 1
)
go
create table Account_Types
(
	account_type_code int primary key identity(1,1),
	account_type_description nvarchar(50) not null
)
go
create table Accounts
(
	account_id int primary key identity(1,1),
	customer_id int foreign key (customer_id) references Customers (customer_id),
	account_type_code int foreign key (account_type_code) references Account_Types (account_type_code),
	account_name nvarchar(50) unique not null,
	current_balace float
)
go
create table Services
(
	service_code int primary key identity(1,1),
	service_description nvarchar(50) not null
--services: Funds Transfer, Send Cheque Book, Stop Cheque Payment
)
go
create table Transaction_Types
(
	transaction_type_code int primary key identity(1,1),
	transaction_description nvarchar(50) not null
--transaction type: Transfer, Withdrowal...
)
go
create table Transactions
(
	transaction_id int primary key identity(1,1),
	account_id int foreign key (account_id) references Accounts (account_id),
	service_code int foreign key (service_code) references Services (service_code),
	transaction_type_code int foreign key (transaction_type_code) references Transaction_Types (transaction_type_code),
	date_of_transaction datetime default getdate() not null,
	amount_of_transaction float not null,
	recipient_address nvarchar(50),
	recipient_account nvarchar(50),
	status int not null
)

insert into Customer_Types values ('Admin')
insert into Customer_Types values ('User')

insert into Customers values (1,'Doan Tu','01/12/1989','Ha Noi','+84987916589','tudn04294@gmail.com',0102686359,120189,'1289',1)
insert into Customers values (2,'Ngo Tri Linh','05/05/1988','Bac Giang','+84985138818','linhnt04255@gmail.com',0102686350,11111,'1234',1)

insert into Account_Types values ('AccType01','Deposit')
insert into Account_Types values ('AccType02','Credit')
insert into Account_Types values ('AccType03','Card')

insert into Accounts values ('Acc01','Cus01','AccType01','abgtc',12000)
insert into Accounts values ('Acc02','Cus01','AccType01','bxd',15000)
select * from Customers

--Check userId and password
create proc pr_Login
	@user_id int,
	@pass int
as
select * from Customers where [user_id] = @user_id and password = @pass and status = 1
--Check userId
create proc pr_Login1
	@user_id int
as
select * from Customers where [user_id] = @user_id and status =1

--Lock Account
create proc pr_Lock_Acc
	@user_id int
as
update Customers set status = 0 where [user_id] = @user_id

--Open Account
create proc pr_Open_Acc
	@user_id int
as
update Customers set status = 1 where [user_id] = @user_id
